/**
 * 
 */
package wpmp.security.mgr.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.hibernate.criterion.Restrictions;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Component;

import wpmp.security.mgr.base.BaseDao;
import wpmp.security.mgr.entity.User;

/**
 * @author Wayne.Wang<5waynewang@gmail.com>
 * @since 5:15:23 PM Mar 15, 2013
 */
@Component
public class UserDao extends BaseDao<User, Long> {

	private static final String SQL_CHANGE_TO_UNGROUPED_ORG = "UPDATE sys_user SET org_id=2 WHERE org_id IN (SELECT id FROM sys_org WHERE level LIKE concat(?,'%'))";

	private static final String SQL_INSERT_USER_ROLE_RELA = "INSERT INTO sys_user_role_rela(user_id,role_id) VALUES (?,?)";

	private static final String SQL_INSERT_USER_ROLE_RELA2 = "INSERT INTO sys_user_role_rela(user_id,role_id) SELECT ?,? FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_user_role_rela WHERE user_id=? AND role_id=?)";

	private static final String SQL_DELETE_USER_ROLE_RELA = "DELETE FROM sys_user_role_rela WHERE user_id=?";

	private static final String SQL_DELETE_USER_ROLE_RELA2 = "DELETE FROM sys_user_role_rela WHERE user_id=? AND role_id=?";

	public User getByLoginName(String loginName) {
		return queryForObject(Restrictions.eq("loginName", loginName));
	}

	/**
	 * 根据level条件like匹配,更改用户的组织/单位至未分组
	 * 
	 * @param orgLevel
	 * @return
	 */
	public Integer changeToUngroupedOrgByLevel(final String orgLevel) {
		return getJdbcTemplate().execute(SQL_CHANGE_TO_UNGROUPED_ORG,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setString(1, orgLevel);
						return null;
					}
				});
	}

	/**
	 * 删除用户与角色的关系
	 * 
	 * @param userId
	 * @return
	 */
	public Integer deleteUserRoleRela(final Long userId) {
		return getJdbcTemplate().execute(SQL_DELETE_USER_ROLE_RELA,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, userId);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 删除用户与角色的关系
	 * 
	 * @param userIds
	 * @return
	 */
	public int[] deleteUserRoleRela(final Long[] userIds) {
		return getJdbcTemplate().batchUpdate(SQL_DELETE_USER_ROLE_RELA,
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setLong(1, userIds[i]);
					}

					@Override
					public int getBatchSize() {
						return userIds.length;
					}
				});
	}

	/**
	 * 删除用户与角色的关系
	 * 
	 * @param userId
	 * @param roleId
	 * @return
	 */
	public Integer deleteUserRoleRela(final Long userId, final Long roleId) {
		return getJdbcTemplate().execute(SQL_DELETE_USER_ROLE_RELA2,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, userId);
						ps.setLong(2, roleId);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 删除用户与角色的关系
	 * 
	 * @param userIds
	 * @return
	 */
	public int[] deleteUserRoleRela(final Long[] userIds, final Long[] roleIds) {
		return getJdbcTemplate().batchUpdate(SQL_DELETE_USER_ROLE_RELA2,
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setLong(1, userIds[i / roleIds.length]);
						ps.setLong(2, roleIds[i % roleIds.length]);
					}

					@Override
					public int getBatchSize() {
						return userIds.length * roleIds.length;
					}
				});
	}

	/**
	 * 新增用户与角色的关系
	 * 
	 * @param userId
	 * @return
	 */
	public Integer insertUserRoleRela(final Long userId, final Long roleId) {
		return getJdbcTemplate().execute(SQL_INSERT_USER_ROLE_RELA,
				new PreparedStatementCallback<Integer>() {
					@Override
					public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException,
							DataAccessException {
						ps.setLong(1, userId);
						ps.setLong(2, roleId);
						return ps.executeUpdate();
					}
				});
	}

	/**
	 * 新增用户与角色的关系
	 * 
	 * @param userIds
	 * @param roleIds
	 * @return
	 */
	public int[] insertUserRoleRelaWhereNotExists(final Long[] userIds, final Long[] roleIds) {
		return getJdbcTemplate().batchUpdate(SQL_INSERT_USER_ROLE_RELA2,
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						Long userId = userIds[i / roleIds.length];
						Long roleId = roleIds[i % roleIds.length];
						ps.setLong(1, userId);
						ps.setLong(2, roleId);
						ps.setLong(3, userId);
						ps.setLong(4, roleId);
					}

					@Override
					public int getBatchSize() {
						return userIds.length * roleIds.length;
					}
				});
	}

	/**
	 * 新增用户与角色的关系
	 * 
	 * @param userId
	 * @param roleIds
	 * @return
	 */
	public int[] insertUserRoleRela(final Long userId, final Long[] roleIds) {
		return getJdbcTemplate().batchUpdate(SQL_INSERT_USER_ROLE_RELA,
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setLong(1, userId);
						ps.setLong(2, roleIds[i]);
					}

					@Override
					public int getBatchSize() {
						return roleIds.length;
					}
				});
	}
}
